/* 
 * Copyright 2011-2012 Andrew Brock
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.catchwa.myvideobrowser.db;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.catchwa.myvideobrowser.model.AlternateMatch;
import org.catchwa.myvideobrowser.model.Movie;

/**
 *
 * @author Andrew Brock
 */
public class SqliteDatabase implements DatabaseFacade {

  private static Logger log = Logger.getLogger(SqliteDatabase.class);
  private static String dbPath = System.getenv("APPDATA") + "\\MyVideoBrowser\\database.sqlite";
  
  public SqliteDatabase() {
    try {
      Class.forName("org.sqlite.JDBC");
      File mvbSettingsDir = new File(System.getenv("APPDATA") + "\\MyVideoBrowser");
      if (!mvbSettingsDir.exists()) {
        mvbSettingsDir.mkdir();
      }
      File test = new File(dbPath);
      if (!test.exists()) {
        init();
      }
    } catch (ClassNotFoundException e) {
      log.error("JDBC initialisation error", e);
    }
  }

  @Override
  public void add(Movie m) {
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      conn.setAutoCommit(false);
      PreparedStatement prep = conn.prepareStatement("insert into movies values (?,?,?,?,?,?,?,?,?);");
      prep.setBytes(1, getBytes(m.getFile()));
      prep.setString(2, m.getTitle());
      prep.setString(3, m.getReleased());
      prep.setString(4, m.getOverview());
      prep.setFloat(5, m.getTmdbRating());
      prep.setInt(6, m.getRuntime());
      prep.setBoolean(7, m.getWatched());
      prep.setString(8, m.getPosterUrl());
      prep.setBytes(9, getBytes(m.getAlternateMatches()));
      prep.executeUpdate();
      conn.commit();
      conn.close();
    } catch (SQLException e) {
      log.error("SQLException adding movie to database", e);
    }
  }

  @Override
  public Movie get(String title) {
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      conn.setAutoCommit(false);
      PreparedStatement prep = conn.prepareStatement("select * from movies where title = ?;");
      prep.setString(1, title);
      ResultSet results = prep.executeQuery();
      Movie m = null;
      if (results.next()) {
        m = getMovieFromResultSet(results);
      } else {
        log.error("Database synchronisation error, no entry was returned");
      }
      conn.commit();
      conn.close();
      return m;
    } catch (SQLException e) {
      log.error("SQLException getting movie from database", e);
    }
    return null;
  }

  @Override
  public void flush() {
    File db = new File("MyVideoBrowser.sqlite");
    if (db.exists()) {
      try {
        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
        Statement stat = conn.createStatement();
        stat.executeUpdate("drop table if exists movies;");
        conn.close();
      } catch (SQLException e) {
        log.error("SQLException dropping the database", e);
      }
      db.delete();
    }
  }

  @Override
  public void init() {
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      Statement stat = conn.createStatement();
      stat.executeUpdate("create table movies (file,title,released,overview,tmdbrating,runtime,watched,posterurl,alternatematches);");
      conn.close();
    } catch (SQLException e) {
      log.error("SQLException initialising the database", e);
    }
  }

  @Override
  public List<Movie> getAll() {
    ArrayList<Movie> movies = new ArrayList<Movie>();
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      Statement stat = conn.createStatement();
      ResultSet results = stat.executeQuery("select * from movies;");
      while (results.next()) {
        movies.add(getMovieFromResultSet(results));
      }
      conn.close();

    } catch (SQLException e) {
      log.error("SQLException getting all entries from the database", e);
    }
    return movies;
  }

  private Movie getMovieFromResultSet(ResultSet results) {
    Movie m = null;
    try {
      byte[] fileBytes = results.getBytes("file");
      File file = null;
      file = getObject(fileBytes, file);
      byte[] alternateMatchesBytes = results.getBytes("alternatematches");
      AlternateMatch[] alternateMatches = null;
      alternateMatches = getObject(alternateMatchesBytes, alternateMatches);
      m = new Movie(file, results.getString("title"), results.getString("released"), results.getString("overview"), results.getFloat("tmdbrating"), results.getInt("runtime"), results.getBoolean("watched"), results.getString("posterurl"), alternateMatches);
    } catch (SQLException e) {
      log.error("SQLException translating a ResultSet to a Movie", e);
    }
    return m;
  }

  @Override
  public void remove(Movie m) {
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      conn.setAutoCommit(false);
      PreparedStatement prep = conn.prepareStatement("delete from movies where file=?;");
      prep.setBytes(1, getBytes(m.getFile()));
      prep.executeUpdate();
      conn.commit();
      conn.close();
    } catch (SQLException e) {
      log.error("SQLException removing an entry from the database", e);
    }
  }

  private static byte[] getBytes(Object obj) {
    byte[] data = null;
    try {
      ByteArrayOutputStream bos = new ByteArrayOutputStream();
      ObjectOutputStream oos = new ObjectOutputStream(bos);
      oos.writeObject(obj);
      oos.flush();
      oos.close();
      bos.close();
      data = bos.toByteArray();
    } catch (IOException e) {
      log.error("Error serialising an object", e);
    }
    return data;
  }

  private static <T> T getObject(byte[] bytes, T object) {
    ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
    try {
      ObjectInput in = new ObjectInputStream(bis);
      object = (T) in.readObject();
      bis.close();
      in.close();
    } catch (ClassNotFoundException e) {
      log.error("Error deserialising an object", e);
    } catch (IOException e) {
      log.error("Error deserialising an object", e);
    }
    return object;
  }

  public void toggleWatched(Movie m) {
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
      conn.setAutoCommit(false);
      PreparedStatement prep = conn.prepareStatement("update movies set watched=? where file=?;");
      prep.setBoolean(1, m.getWatched());
      prep.setBytes(2, getBytes(m.getFile()));
      prep.executeUpdate();
      conn.commit();
      conn.close();
    } catch (SQLException e) {
      log.error("SQLException removing an entry from the database", e);
    }
  }
}
